package com.system.data.dao;

import com.system.data.model.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface YmtInfoDao {

    @Select("select * from ymt_products_info;")
    List<YmtProductsInfo> findAllYmtProductsInfo();

    @Select("select * from ymt_products_varieties_info;")
    List<YmtProductsVarietiesInfo> findAllYmtProductsVarietiesInfo();

    @Select("select * from ymt_products_origin_info;")
    List<YmtProductsOriginInfo> findAllYmtProductsOriginInfo();

    @Select("select * from ymt_products_info where marketDate=#{date} and cateName=#{cateName}")
    List<YmtProductsInfo> findByDateCate(@Param("date") String date, @Param("cateName") String cateName);

    @Select("select * from ymt_products_info where marketDate between #{start} and #{end} and cateName=#{cateName}")
    List<YmtProductsInfo> findByStartEnd(@Param("start") String start, @Param("end") String end, @Param("cateName") String cateName);

    @Select("select DISTINCT * from ymt_products_origin_info where marketDate between #{start} and #{end} and cateName=#{cateName} and origin=#{origin}")
    List<YmtProductsOriginInfo> findOriginByStartEnd(@Param("start") String start, @Param("end") String end, @Param("cateName") String cateName,@Param("origin") String origin);

    @Select("select distinct * from ymt_products_origin_info where cateName=#{cateName} and marketDate=#{marketDate}")
    List<YmtProductsOriginInfo> findAllOrigin(@Param("marketDate") String marketDate, @Param("cateName") String cateName);

    @Select("select * from ymt_products_sichuan_jiangxi_info where marketDate=#{marketDate} and cateName=#{cateName} and province=#{province}")
    List<YmtProductsSichuanJiangxiInfo> findSiChuanJiangXi(@Param("marketDate") String marketDate, @Param("cateName") String cateName, @Param("province") String province);

    @Select("SELECT * FROM ymt_products_info WHERE marketDate=#{marketDate} and species=#{species}")
    List<YmtProductsInfo> findBySpecies(@Param("marketDate") String marketDate, @Param("species") String species);

//    @Select("SELECT DISTINCT * FROM ymt_products_info WHERE species=#{species}")
//    List<YmtProductsInfo> findBySpecies(@Param("marketDate") String marketDate, @Param("species") String species);

    @Select("SELECT * FROM `ymt_products_hot` where marketDate=#{marketDate} order by buyerHeat LIMIT 20")
    List<YmtProductsHot> findHotByBuyer(@Param("marketDate") String marketDate);


    @Select("SELECT * FROM `ymt_products_hot` where marketDate=#{marketDate} order by SellerHeat LIMIT 20")
    List<YmtProductsHot> findHotBySeller(@Param("marketDate") String marketDate);

    @Select("SELECT * FROM `ymt_products_origin_info` WHERE species=#{species} and marketDate=#{marketDate} and origin=#{origin};")
    List<YmtProductsOriginInfo> findByDateOriginSpecies(@Param("marketDate") String marketDate, @Param("origin") String origin, @Param("species") String species);

    @Select("SELECT DISTINCT * FROM `ymt_products_origin_info` WHERE cateName=#{cateName} and marketDate=#{marketDate} and origin=#{origin};")
    List<YmtProductsOriginInfo> findByDateOriginCate(@Param("marketDate") String marketDate, @Param("origin") String origin, @Param("cateName") String cateName);

    @Select("select * from ymt_products_origin_info order by marketDate DESC limit 5;")
    List<YmtProductsOriginInfo> findOrderByMarketDate();

    @Select("select * from ymt_products_origin_info where cateName=#{cateName} and marketDate=#{marketDate} and originAvgPrice=(select max(originAvgPrice) from ymt_products_origin_info where cateName=#{cateName} and marketDate=#{marketDate});")
    List<YmtProductsOriginInfo> findMaxPriceOrigin(@Param("marketDate") String marketDate, @Param("cateName") String cateName);

    @Select("select * from ymt_products_origin_info where cateName=#{cateName} and marketDate=#{marketDate} and originAvgPrice=(select min(originAvgPrice) from ymt_products_origin_info where cateName=#{cateName} and marketDate=#{marketDate});")
    List<YmtProductsOriginInfo> findMinPriceOrigin(@Param("marketDate") String marketDate, @Param("cateName") String cateName);
}

